package com.ruoyi.system.utils;

import com.jcraft.jsch.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.nio.file.Files;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.*;

/**
 * 数据库同步与更新工具类
 * 提供数据库本地导出、远程传输、导入及更新的完整流程
 */
public class MySQLSyncAndUpdate {
    private static final Logger logger = LoggerFactory.getLogger(MySQLSyncAndUpdate.class);

    // 线程池 - 改为实例级，避免静态共享导致的问题
    private final ExecutorService executor = new ThreadPoolExecutor(
            0, Integer.MAX_VALUE,
            60L, TimeUnit.SECONDS,
            new SynchronousQueue<>(),
            runnable -> {
                Thread thread = new Thread(runnable, "mysql-sync-thread");
                thread.setDaemon(false);
                return thread;
            },
            new ThreadPoolExecutor.CallerRunsPolicy() // 当线程池关闭时，在当前线程执行任务
    );

    // 配置信息
    private static final String SSH_USER = "psw";
    private static final String SSH_PASSWORD = "psw123456";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "root";
    private static final String DATABASE_NAME = "ventilator";
    private static final int SSH_PORT = 22;
    private static final String LOCAL_TEMP_DIR = "/tmp/";
    private static final String REMOTE_TEMP_DIR = "/tmp/";

    // 命令路径（动态获取）
    private static final String LOCAL_MYSQLDUMP_PATH = getCommandPath("mysqldump");
    private static final String REMOTE_MYSQL_PATH = "mysql";

    // 主机SQL配置
    private static final Map<String, String[]> HOST_SQL_CONFIG = new HashMap<>(2);
    static {
        HOST_SQL_CONFIG.put("124", new String[]{"true", "124主机"});
        HOST_SQL_CONFIG.put("125", new String[]{"false", "125主机"});
    }
    private static final String SQL_TEMPLATE = "UPDATE sys_config SET config_value='%s',remark='%s' WHERE config_key='sys.account.dataEnabled'";

    // 超时与重试配置
    private static final int CMD_TIMEOUT = 120000;
    private static final int NORMAL_TIMEOUT = 15000;
    private static final int VERSION_TIMEOUT = 10000;
    private static final int THREAD_SHUTDOWN_TIMEOUT = 5000;
    private static final int MAX_RETRIES = 2;
    private static final int RETRY_DELAY = 2000;

    // SFTP相关配置
    private static final int SFTP_TIMEOUT = 15000;
    private static final int CHANNEL_TIMEOUT = 5000;
    private static final int SSH_TIMEOUT = 30000;
    private static final int BUFFER_SIZE = 1024;

    // 进度监控（精简日志）
    private static class ProgressMonitor implements SftpProgressMonitor {
        private final String source;
        private final String destination;
        private long totalSize;
        private long transferred;
        private int lastPercent = -1;

        public ProgressMonitor(String source, String destination) {
            this.source = source;
            this.destination = destination;
        }

        @Override
        public void init(int op, String src, String dest, long max) {
            this.totalSize = max;
            this.transferred = 0;
            String sizeStr = (totalSize <= 0) ? "未知" : formatFileSize(totalSize);
            logger.info("开始传输文件: {} -> {} (总大小: {})", source, destination, sizeStr);
        }

        @Override
        public boolean count(long count) {
            transferred += count;
            if (totalSize > 0) {
                int percent = (int) (transferred * 100.0 / totalSize);
                // 只打印关键进度点
                if (percent != lastPercent && (percent % 20 == 0 || percent >= 100)) {
                    lastPercent = percent;
                    logger.info("传输进度: {}%", percent);
                }
            }
            return true;
        }

        @Override
        public void end() {
            logger.info("文件传输完成: {} -> {} (实际传输: {})", source, destination, formatFileSize(transferred));
        }

        private String formatFileSize(long bytes) {
            if (bytes < 1024) return bytes + "B";
            if (bytes < 1024 * 1024) return String.format("%.1fKB", bytes / 1024.0);
            return String.format("%.2fMB", bytes / (1024.0 * 1024.0));
        }
    }

    // 流处理工具（仅记录错误）
    private static class StreamHandler extends Thread {
        private final InputStream is;
        private final boolean isError;

        public StreamHandler(InputStream is, boolean isError) {
            this.is = is;
            this.isError = isError;
        }

        @Override
        public void run() {
            try (BufferedReader br = new BufferedReader(new InputStreamReader(is))) {
                String line;
                while ((line = br.readLine()) != null) {
                    if (isError) {
                        logger.error("命令错误输出: {}", line);
                    }
                    // 忽略普通输出，减少日志量
                }
            } catch (IOException e) {
                logger.error("流处理异常", e);
            }
        }
    }

    /**
     * 动态获取本地命令路径
     */
    private static String getCommandPath(String command) {
        try {
            Process process = Runtime.getRuntime().exec(new String[]{"bash", "-c", "which " + command});
            if (process.waitFor(1000, TimeUnit.MILLISECONDS) && process.exitValue() == 0) {
                try (BufferedReader reader = new BufferedReader(new InputStreamReader(process.getInputStream()))) {
                    String path = reader.readLine();
                    if (path != null && !path.isEmpty()) {
                        logger.debug("自动获取{}路径: {}", command, path);
                        return path;
                    }
                }
            }
        } catch (Exception e) {
            logger.debug("获取{}路径失败，使用命令名", command);
        }
        return command;
    }

    /**
     * 核心同步方法（精简日志）
     */
    public String run(String sourceHost, String targetHost) {
        long startTime = System.currentTimeMillis();
        String dumpFile = null;
        try {
            logger.info("===== 开始同步流程 [源={}, 目标={}] =====", sourceHost, targetHost);

            // 步骤1: 本地导出
            logger.info("步骤1/4: 开始本地数据库导出");
            dumpFile = exportLocalDb();
            logger.info("步骤1/4: 本地导出完成 [文件={}]", dumpFile);

            // 步骤2: 传输文件
            logger.info("步骤2/4: 开始传输文件到远程");
            transferFile(dumpFile, targetHost);
            logger.info("步骤2/4: 文件传输完成");

            // 步骤3: 远程导入
            logger.info("步骤3/4: 开始远程数据库导入");
            importToRemote(dumpFile, targetHost);
            logger.info("步骤3/4: 远程导入完成");

            // 步骤4: 执行SQL更新
            logger.info("步骤4/4: 开始执行远程SQL更新");
            executeRemoteUpdate(targetHost);
            logger.info("步骤4/4: SQL更新完成");

            long totalTime = System.currentTimeMillis() - startTime;
            logger.info("===== 同步流程全部完成 [总耗时={}ms] =====", totalTime);
            return "同步完成";
        } catch (Exception e) {
            long totalTime = System.currentTimeMillis() - startTime;
            String msg = "同步失败: " + e.getMessage();
            logger.error("===== 同步流程中断 [总耗时={}ms] =====", totalTime, e);
            return msg;
        } finally {
            logger.info("开始清理临时文件");
            if (dumpFile != null) {
                // 先执行清理操作，确保在线程池关闭前完成
                deleteRemoteFile(dumpFile, targetHost);
                cleanupLocalFile(dumpFile);
            }
            logger.info("清理完成");
            // 所有操作完成后再关闭线程池
            shutdownAfterCompletion();
        }
    }

    /**
     * 所有任务完成后手动关闭线程池
     */
    public void shutdownAfterCompletion() {
        try {
            executor.shutdown();
            if (!executor.awaitTermination(THREAD_SHUTDOWN_TIMEOUT, TimeUnit.MILLISECONDS)) {
                executor.shutdownNow();
                logger.warn("线程池强制关闭");
            } else {
                logger.info("线程池已正常关闭");
            }
        } catch (InterruptedException e) {
            executor.shutdownNow();
            Thread.currentThread().interrupt();
            logger.error("线程池关闭被中断", e);
        }
    }

    /**
     * 本地导出数据库
     */
    private String exportLocalDb() throws Exception {
        String dumpFile = LOCAL_TEMP_DIR + "db_dump_" + System.currentTimeMillis() + ".sql";

        // 验证命令可用性（仅记录错误）
        verifyCommand(LOCAL_MYSQLDUMP_PATH, "mysqldump", true);

        // 执行导出命令
        String errorLog = dumpFile + ".error.log";
        String cmd = String.format(
                "%s -u%s -p%s --databases %s --log-error=%s 2>&1 > %s",
                LOCAL_MYSQLDUMP_PATH, DB_USER, escapePwd(), DATABASE_NAME, errorLog, dumpFile
        );

        Process process = Runtime.getRuntime().exec(new String[]{"bash", "-c", cmd});
        new StreamHandler(process.getInputStream(), false).start();
        new StreamHandler(process.getErrorStream(), true).start();

        if (!process.waitFor(CMD_TIMEOUT, TimeUnit.MILLISECONDS)) {
            process.destroyForcibly();
            throw new TimeoutException("导出超时");
        }

        if (process.exitValue() != 0) {
            String errorMsg = "导出失败（状态码: " + process.exitValue() + "）";
            File logFile = new File(errorLog);
            if (logFile.exists() && logFile.length() > 0) {
                errorMsg += "，详见错误日志: " + errorLog;
            }
            throw new Exception(errorMsg);
        }

        File file = new File(dumpFile);
        if (!file.exists() || file.length() == 0) {
            throw new Exception("导出文件为空或不存在");
        }

        double fileSizeMB = file.length() / (1024.0 * 1024.0);
        String sizeStr = String.format("%.2fMB", fileSizeMB);
        logger.info("导出文件大小: {}", sizeStr);
        return dumpFile;
    }

    /**
     * 传输文件到远程主机
     */
    private void transferFile(String localFile, String targetHost) throws Exception {
        File file = new File(localFile);
        if (!file.exists()) {
            throw new FileNotFoundException("本地文件不存在: " + localFile);
        }

        String fileName = file.getName();
        String remoteFilePath = REMOTE_TEMP_DIR + fileName;

        Session session = null;
        ChannelSftp sftp = null;
        try {
            session = getSshSession(targetHost);
            sftp = (ChannelSftp) session.openChannel("sftp");
            sftp.connect(SFTP_TIMEOUT);

            // 确保远程目录存在
            try {
                sftp.stat(REMOTE_TEMP_DIR);
            } catch (SftpException e) {
                if (e.id == ChannelSftp.SSH_FX_NO_SUCH_FILE) {
                    sftp.mkdir(REMOTE_TEMP_DIR);
                    logger.info("创建远程目录: {}", REMOTE_TEMP_DIR);
                } else {
                    throw new Exception("远程目录检查失败: " + e.getMessage());
                }
            }

            try (InputStream is = Files.newInputStream(file.toPath())) {
                sftp.put(is, remoteFilePath, new ProgressMonitor(fileName, targetHost + ":" + REMOTE_TEMP_DIR), ChannelSftp.OVERWRITE);
            }
            // 验证文件是否存在
            sftp.stat(remoteFilePath);
        } catch (SftpException e) {
            throw new Exception("SFTP操作失败: " + e.getMessage() + "（错误码: " + e.id + "）");
        } finally {
            if (sftp != null && sftp.isConnected()) {
                sftp.disconnect();
            }
            if (session != null && session.isConnected()) {
                session.disconnect();
            }
        }
    }

    /**
     * 导入到远程数据库
     */
    private void importToRemote(String filePath, String targetHost) throws Exception {
        String fileName = new File(filePath).getName();
        String remoteFilePath = REMOTE_TEMP_DIR + fileName;

        Session session = null;
        try {
            session = getSshSession(targetHost);
            verifyCommand(REMOTE_MYSQL_PATH, "mysql", false, session);

            // 验证远程文件存在性
            String testResult = executeRemoteCmd(session, "test -f " + remoteFilePath + " && echo 0 || echo 1", NORMAL_TIMEOUT);
            if (!"0".equals(testResult.trim())) {
                throw new Exception("远程文件不存在: " + remoteFilePath);
            }

            // 执行导入命令
            String cmd = String.format(
                    "bash -c ' %s -u%s -p%s -e \"CREATE DATABASE IF NOT EXISTS %s;\" 2>&1; " +
                            "if [ $? -ne 0 ]; then exit 1; fi; " +
                            "%s -u%s -p%s %s < %s 2>&1; exit $? '",
                    REMOTE_MYSQL_PATH, DB_USER, escapePwd(), DATABASE_NAME,
                    REMOTE_MYSQL_PATH, DB_USER, escapePwd(), DATABASE_NAME, remoteFilePath
            );

            executeRemoteCmd(session, cmd, CMD_TIMEOUT, MAX_RETRIES);
        } finally {
            if (session != null && session.isConnected()) {
                session.disconnect();
            }
        }
    }

    /**
     * 执行远程数据库更新
     */
    private void executeRemoteUpdate(String targetHost) throws SQLException {
        String[] config = HOST_SQL_CONFIG.entrySet().stream()
                .filter(entry -> targetHost.contains(entry.getKey()))
                .map(Map.Entry::getValue)
                .findFirst()
                .orElseThrow(() -> new SQLException("未找到主机配置: " + targetHost));
        String sql = String.format(SQL_TEMPLATE, config[0], config[1]);

        String jdbcUrl = String.format(
                "jdbc:mysql://%s:3306/%s?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC",
                targetHost, DATABASE_NAME
        );

        try (Connection conn = DriverManager.getConnection(jdbcUrl, DB_USER, DB_PASSWORD);
             Statement stmt = conn.createStatement()) {
            int rows = stmt.executeUpdate(sql);
            logger.info("SQL更新影响行数: {}", rows);
        }
    }

    /**
     * 验证命令可用性（精简日志）
     */
    private void verifyCommand(String cmdPath, String cmdName, boolean isLocal, Session... session) throws Exception {
        // 检查线程池状态
        if (executor.isShutdown() || executor.isTerminated()) {
            throw new Exception("线程池已关闭，无法执行" + cmdName + "命令验证");
        }

        try {
            String versionCmd = cmdPath + " --version";
            if (isLocal) {
                executeLocalCmd(versionCmd, VERSION_TIMEOUT);
            } else {
                executeRemoteCmd(session[0], versionCmd, VERSION_TIMEOUT);
            }
        } catch (Exception e) {
            throw new Exception(cmdName + "命令验证失败: " + e.getMessage());
        }
    }

    /**
     * 执行本地命令
     */
    private void executeLocalCmd(String cmd, int timeout) throws Exception {
        Process process = Runtime.getRuntime().exec(new String[]{"bash", "-c", cmd});

        // 只处理错误流
        new Thread(() -> {
            try (BufferedReader br = new BufferedReader(new InputStreamReader(process.getErrorStream()))) {
                String line;
                while ((line = br.readLine()) != null) {
                    logger.error("本地命令错误: {}", line);
                }
            } catch (IOException e) {
                logger.error("本地命令流异常", e);
            }
        }).start();

        if (!process.waitFor(timeout, TimeUnit.MILLISECONDS)) {
            process.destroyForcibly();
            throw new TimeoutException("本地命令超时");
        }

        if (process.exitValue() != 0) {
            throw new Exception("本地命令执行失败（状态码: " + process.exitValue() + "）");
        }
    }

    /**
     * 执行远程命令（带重试）
     */
    private String executeRemoteCmd(Session session, String cmd, int timeout, int... retries) throws Exception {
        // 检查线程池状态
        if (executor.isShutdown() || executor.isTerminated()) {
            throw new Exception("线程池已关闭，无法执行远程命令");
        }

        int maxRetries = retries.length > 0 ? retries[0] : 1;
        Exception lastEx = new Exception("命令执行失败");

        for (int i = 0; i < maxRetries; i++) {
            try {
                return executeRemoteCmd(session, cmd, timeout);
            } catch (TimeoutException e) {
                lastEx = e;
                if (i < maxRetries - 1) {
                    logger.warn("命令超时，重试({}/{})", i + 1, maxRetries);
                    Thread.sleep(RETRY_DELAY);
                }
            } catch (Exception e) {
                lastEx = e;
                break;
            }
        }
        throw lastEx;
    }

    /**
     * 执行远程命令（核心实现）
     */
    private String executeRemoteCmd(Session session, String cmd, int timeout) throws Exception {
        Callable<String> task = () -> {
            ChannelExec channel = (ChannelExec) session.openChannel("exec");
            channel.setCommand(cmd);
            channel.setInputStream(null);

            InputStream is = channel.getInputStream();
            InputStream err = channel.getErrStream();
            channel.connect(CHANNEL_TIMEOUT);

            byte[] buffer = new byte[BUFFER_SIZE];
            StringBuilder output = new StringBuilder();
            StringBuilder errorOutput = new StringBuilder();
            long lastActive = System.currentTimeMillis();

            while (true) {
                if (System.currentTimeMillis() - lastActive > timeout) {
                    throw new TimeoutException("远程命令超时");
                }

                if (channel.isClosed()) {
                    // 读取剩余输出
                    while (is.available() > 0) {
                        int len = is.read(buffer);
                        if (len > 0) output.append(new String(buffer, 0, len));
                    }
                    while (err.available() > 0) {
                        int len = err.read(buffer);
                        if (len > 0) errorOutput.append(new String(buffer, 0, len));
                    }
                    break;
                }

                // 读取输出
                if (is.available() > 0) {
                    int len = is.read(buffer);
                    if (len > 0) {
                        output.append(new String(buffer, 0, len));
                        lastActive = System.currentTimeMillis();
                    }
                }
                // 读取错误输出
                if (err.available() > 0) {
                    int len = err.read(buffer);
                    if (len > 0) {
                        errorOutput.append(new String(buffer, 0, len));
                        lastActive = System.currentTimeMillis();
                    }
                } else {
                    Thread.sleep(100);
                }
            }

            int exitStatus = channel.getExitStatus();
            if (exitStatus != 0) {
                throw new IOException("远程命令失败（状态码: " + exitStatus + "）: " + errorOutput.toString().trim());
            }
            return output.toString();
        };

        Future<String> future;
        try {
            // 再次检查线程池状态
            if (executor.isShutdown() || executor.isTerminated()) {
                throw new Exception("线程池已关闭，无法提交任务");
            }
            future = executor.submit(task);
        } catch (RejectedExecutionException e) {
            throw new Exception("任务提交失败，线程池可能已关闭: " + e.getMessage());
        }

        try {
            return future.get(timeout + 5000, TimeUnit.MILLISECONDS);
        } catch (TimeoutException e) {
            future.cancel(true);
            throw new TimeoutException("远程命令超时");
        } catch (ExecutionException e) {
            throw new Exception("远程命令执行失败: " + e.getCause().getMessage());
        }
    }

    /**
     * 创建SSH会话
     */
    private Session getSshSession(String host) throws JSchException {
        JSch jsch = new JSch();
        Session session = jsch.getSession(SSH_USER, host, SSH_PORT);
        session.setPassword(SSH_PASSWORD);

        Properties config = new Properties();
        config.put("StrictHostKeyChecking", "no");
        config.put("ServerAliveInterval", "10000");
        session.setConfig(config);
        session.setTimeout(SSH_TIMEOUT);
        session.connect();
        return session;
    }

    /**
     * 清理本地文件
     */
    private void cleanupLocalFile(String path) {
        try {
            boolean mainDeleted = new File(path).delete();
            boolean errorDeleted = new File(path + ".error.log").delete();
            logger.debug("本地文件清理: 主文件={}, 错误日志={}", mainDeleted, errorDeleted);
        } catch (Exception e) {
            logger.error("本地文件清理失败", e);
        }
    }

    /**
     * 删除远程文件
     */
    private void deleteRemoteFile(String path, String host) {
        // 检查线程池状态
        if (executor.isShutdown() || executor.isTerminated()) {
            logger.warn("线程池已关闭，无法执行远程文件删除操作");
            return;
        }

        String fileName = new File(path).getName();
        String remoteFilePath = REMOTE_TEMP_DIR + fileName;

        Session session = null;
        try {
            session = getSshSession(host);
            String result = executeRemoteCmd(session, "rm -f " + remoteFilePath + " && echo 0 || echo 1", NORMAL_TIMEOUT, MAX_RETRIES);
            if (!"0".equals(result.trim())) {
                logger.warn("远程文件删除失败: {}", remoteFilePath);
            }
        } catch (Exception e) {
            logger.error("远程文件清理失败: {}", remoteFilePath, e);
        } finally {
            if (session != null && session.isConnected()) {
                session.disconnect();
            }
        }
    }

    /**
     * 密码转义
     */
    private String escapePwd() {
        return DB_PASSWORD.replace("'", "'\\''");
    }
}
